// Created by Xslt generator for Eclipse.
// XSL :  not found (java.io.FileNotFoundException:  (The system cannot find the path specified))
// Default XSL used : easystruts.jar$org.easystruts.xslgen.JavaClass.xsl

package com.ba360.struts.action;

import java.sql.Connection;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.sql.DataSource;

import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

import com.ba360.portal.common.PortalConstants;
import com.ba360.portal.database.DBConnectionImpl;
import com.ba360.portal.database.dbo.Classifieds;
import com.ba360.portal.database.dbo.User;
import com.ba360.struts.form.DealsInfoBean;

/**
 * GetMyAdList.java created by EasyStruts - XsltGen. http://easystruts.sf.net
 * created on 05-04-2004
 * 
 * XDoclet definition:
 * 
 * @struts:action attribute="" input="" prefix="" suffix=""
 */
public class GetMyAdList extends Action {

	// --------------------------------------------------------- Instance
	// Variables

	// --------------------------------------------------------- Methods

	/**
	 * Method execute
	 * 
	 * @param ActionMapping
	 *            mapping
	 * @param ActionForm
	 *            form
	 * @param HttpServletRequest
	 *            request
	 * @param HttpServletResponse
	 *            response
	 * @return ActionForward
	 * @throws Exception
	 */
	public ActionForward execute(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response)
			throws Exception {

		// throw new UnsupportedOperationException("Generated method
		// 'execute(...)' not implemented.");
		HttpSession session = request.getSession();
		User user = (User) session.getAttribute(PortalConstants.USER);
		double userId = user.getUserId();
		Context context = null;
		DataSource ds = null;
		Connection conn = null;
		Statement stmt = null;
		DBConnectionImpl dbConn = new DBConnectionImpl();
		conn = dbConn.getConnection();
		stmt = conn.createStatement();
		Statement stmt1 = conn.createStatement();
		java.sql.ResultSet rs = stmt
				.executeQuery("select * from ads where member_id=" + userId
						+ " order by date_posted desc");
		// Vector myAdList = new Vector();
		List myAdList = new ArrayList();

		while (rs.next()) {
			Classifieds ads = new Classifieds();
			ads.setAdvId(rs.getInt(ads.ADV_ID));
			ads.setActiveDate(rs.getDate(ads.ADV_ACTIVEUNTIL));
			ads.setAdDesc(rs.getString(ads.ADV_TRADEDESC));
			ads.setAdTitle(rs.getString(ads.ADV_TITLE));
			ads.setCityId(rs.getInt(ads.ADV_CITY));
			String city = dLookUp(stmt1, "lookup_city", "cityname",
					"city_id=" + ads.getCityId());
			ads.setCity(city);
			ads.setCatId(rs.getInt(ads.ADV_CATID));
			ads.setContactEmail(rs.getString(ads.ADV_CONTACTEMAIL));
			ads.setContactName(rs.getString(ads.ADV_CONTACTNAME));
			ads.setContactNumber(rs.getString(ads.ADV_CONTACTNUM));
			ads.setDatePosted(rs.getTimestamp(ads.ADV_DATE));
			ads.setHouseType(rs.getString(ads.ADV_HOUSETYPE));
			int parent_cat_id = Integer.parseInt(dLookUp(stmt1, "categories",
					"par_category_id", "category_id=" + ads.getCatId()));
			String parentCatName = dLookUp(stmt1, "categories", "name",
					"category_id=" + parent_cat_id);
			ads.setParentCategoryName(parentCatName);
			ads.setParentCatId(parent_cat_id);
			ads.setPrice(rs.getString(ads.ADV_PRICE));
			ads.setSqFt(rs.getString(ads.ADV_SQFT));
			String subCatName = dLookUp(stmt1, "categories", "name",
					"category_id=" + ads.getCatId());
			ads.setSubCategoryName(subCatName);
			myAdList.add(ads);

		}
		request.setAttribute("MYADLIST", myAdList);
		session.setAttribute("MYADLIST", myAdList);
		// getMyResponses(session);
		rs.close();
		stmt.close();
		stmt1.close();
		// conn.close();
		stmt = conn.createStatement();
		java.sql.ResultSet rsBD = stmt
				.executeQuery("select * from businessdeals where user_id="
						+ userId);
		ArrayList al = new ArrayList();
		while (rsBD.next()) {
			DealsInfoBean diBean = new DealsInfoBean();
			diBean.setTitle(rsBD.getString("title"));
			diBean.setSubCat_id(rsBD.getString("cat_id"));
			String mainCatId = dLookUp(conn.createStatement(),
					"dealscategories", "par_category_id", "category_id="
							+ rsBD.getInt("cat_id"));
			diBean.setCategory_id(mainCatId);
			String mainCatName = dLookUp(conn.createStatement(),
					"dealscategories", "name", "category_id="
							+ Integer.parseInt(mainCatId));
			diBean.setMainCatName(mainCatName);
			String subCatName = dLookUp(conn.createStatement(),
					"dealscategories", "name", "category_id="
							+ rsBD.getInt("cat_id"));
			diBean.setSubCatName(subCatName);

			diBean.setDealsId(rsBD.getInt("deal_id"));
			diBean.setBusinessId(rsBD.getInt("business_id"));
			diBean.setDiscountPrice(rsBD.getString("discount_price"));
			diBean.setDealDesc(rsBD.getString("deal_desc"));
			diBean.setDatePosted(rsBD.getString("date_posted"));
			diBean.setExpiry(rsBD.getString("expiry"));

			// System.out.println("select
			// business_id,business_type,business_name,business_address,city_id,"+
			// "zip,email,website,business_phone,business_desc where
			// business_id="+rsBD.getInt("business_id"));
			Statement stmt2 = conn.createStatement();
			java.sql.ResultSet rs2 = stmt2
					.executeQuery("select business_id,business_user_id,business_type,business_name,business_address,city_id,"
							+ "zip,email,website,business_phone,business_desc from businessinfo where business_id="
							+ rsBD.getInt("business_id"));
			while (rs2.next()) {
				diBean.setPlace_id(rs2.getString("city_id"));
				String placeName = dLookUp(conn.createStatement(),
						"lookup_city", "cityname", "city_id="
								+ rs2.getInt("city_id"));
				diBean.setPlace(placeName);

				diBean.setBusinessAddress(rs2.getString("business_address"));
				diBean.setBusinessDesc(rs2.getString("business_desc"));
				diBean.setBusinessId(rs2.getInt("business_id"));
				diBean.setBusinessName(rs2.getString("business_name"));
				diBean.setBusinessPhone(rs2.getString("business_phone"));

				diBean.setBusinessType(rs2.getString("business_phone"));
				diBean.setEmail(rs2.getString("email"));
				diBean.setZip(rs2.getString("zip"));
				diBean.setWebsite(rs2.getString("website"));
				diBean.setUserId(rs2.getString("business_user_id"));

			}
			rs2.close();
			stmt2.close();
			al.add(diBean);
		}
		session.setAttribute("MyDeals", al);
		conn.close();
		return mapping.findForward("success");
	}

	private String dLookUp(java.sql.Statement stat, String table, String fName,
			String where) {
		try {
			java.sql.ResultSet rs1 = openrs(stat, "SELECT " + fName + " FROM "
					+ table + " WHERE " + where);
			if (!rs1.next()) {
				rs1.close();
				return "";
			}
			String res = rs1.getString(1);
			rs1.close();
			return (res == null ? "" : res);
		} catch (Exception e) {
			return "";
		}
	}

	java.sql.ResultSet openrs(java.sql.Statement stat, String sql)
			throws java.sql.SQLException {
		java.sql.ResultSet rs = stat.executeQuery(sql);
		return (rs);
	}

}